package code20_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test03_PreparedStatement {
	static Connection conn = null;
	// static Statement stmt = null;
	static PreparedStatement ps = null;
	static ResultSet rs = null;

	public static void main(String[] args) {
		// boolean flag = login("tom", "123' or ' 1=1");
		// boolean flag = login("aaa", "123");
		// System.out.println(flag);
		
		User user = new User();
		user.setUsername("yyy");
		user.setPassword("456");
		user.setAge(20);
		try {
			register(user);
			System.out.println("注册成功！");
		} catch (UserRegisterException e) {
			System.out.println(e.getMessage());
		}
	}

	/*
	 * 用户登录
	 */
	public static boolean login(String username, String password) {
		// String sql =
		// "select id,username,password,age from t_user where username='"+username+"' and password='"+password+"'";
		// String sql =
		// "select id,username,password,age from t_user where username=? and password=?";
		// 建议使用StringBuffer或StringBuilder拼接SQL语句，关键字、表名、列名等独占一行
		String sql = new StringBuffer()
			.append(" select ")
			.append(" 	id,username,password,age ")
			.append(" from ")
			.append(" 	t_user ")
			.append(" where ")
			.append(" 	username=? ")
			.append(" 		and ")
			.append(" 	password=? ")
			.toString();
		try {
			conn = JdbcUtil.getConnection();
			// stmt = conn.createStatement();
			// rs = stmt.executeQuery(sql);

			PreparedStatement ps = conn.prepareStatement(sql); // 获取时需要传入sql，进行预编译
			ps.setObject(1, username); // 为占位符?赋值，编号从1开始
			ps.setObject(2, password);
			rs = ps.executeQuery();
			if (rs.next()) { // 如果返回结果集中最多只有一条记录，可以使用if
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, ps, rs);
		}
		return false;
	}

	/*
	 * 用户注册
	 */
	public static void register(User user) throws UserRegisterException{
		String sql = new StringBuffer()
			.append(" insert into ")
			.append(" 	t_user ")
			.append(" 		(username,password,age) ")
			.append(" values ")
			.append(" 		(?,?,?) ")
			.append("  ")
			.toString();
		try {
			conn = JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setInt(3, user.getAge());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new UserRegisterException("注册失败！");
		}finally{
			JdbcUtil.close(conn, ps, rs);
		}
	}
}
